package sk.fiit.vppj.eventmanager.graph;

import java.io.File;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;

import javax.swing.ImageIcon;

import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.RowMapper;

import sk.fiit.vppj.eventmanager.core.DatabaseManager;
import sk.fiit.vppj.eventmanager.core.UserManager;

/**
 * Central class for creating graphs. This class is very modular, just takes data from database.
 * @author Marek
 *
 */
public final class Graph {
	private String graphName = null;
	private String graphGroupBy = null;
	private final static Logger log = Logger.getLogger( Graph.class.getName() );

	public Graph(String graphName, String graphGroupBy) {
		this.graphName = graphName;
		this.graphGroupBy = graphGroupBy;
	}

	/**
	 * Method responsible for choosing correct statement to execute
	 * @return Prepared statement which will execute in order to get data about graph
	 */
	private PreparedStatementCreator getStatement() {
		PreparedStatementCreator statement = null;
		if( this.graphName.equals( "BIRTHDAY" ) ) {
			statement = getBirthDateSt();
		}
		else if( this.graphName.equals( "REGISTRATION" ) ) {
			statement = getRegistrationSt();
		}
		else if( this.graphName.equals( "LAST_VISIT" ) ) {
			statement = getLastVisitSt();
		}
		else if( this.graphName.equals( "PARTICIPANTS_PER_STATES" ) ) {
			statement = getParticipantsStatesSt();
		}
		else if( this.graphName.equals( "FUNTRIP_VISITORS" ) ) {
			statement = getFuntripVisitorsSt();
		}
		else if( this.graphName.equals( "ESITOUR_VISITORS" ) ) {
			statement = getEsiTourVisitorsSt();
		}
		else if( this.graphName.equals( "PROJECTS_SCORE" ) ) {
			statement = getProjectScoreSt();
		}
		else if( this.graphName.equals( "ACCOUNT_TYPE" ) ) {
			statement = getAccountTypeSt();
		}
		else {
			log.log( Level.WARNING, "Graph name doesn't fit. Can't choose correct sql query!" );
			throw new IllegalStateException( "Graph name doesn't fit. Can't choose correct sql query!" );
		}

		return statement;

	}

	private PreparedStatementCreator getAccountTypeSt() {
		return new PreparedStatementCreator() {
			String sql = parseTimeMeasuredQuery( "SELECT COUNT(*) AS y, class AS x FROM jos_event_manager_users WHERE jos_event_manager_users.event_id = ? GROUP BY class ORDER BY y" );

			@Override
			public PreparedStatement createPreparedStatement(Connection con) throws SQLException {

				PreparedStatement ps = con.prepareStatement( sql );
				ps.setInt( 1, UserManager.getInstance().getUser().getEvent_id() );
				return ps;
			}
		};
	}

	private PreparedStatementCreator getFuntripVisitorsSt() {

		return new PreparedStatementCreator() {
			String sql = "SELECT trips.name AS x,  COUNT( users.funtrip_id ) AS y FROM jos_event_manager_trips AS trips "
					+ "LEFT JOIN jos_event_manager_users AS users ON users.funtrip_id = trips.IDTrip"
					+ " WHERE trips.event_id = ? AND trips.type = 'FUNTRIP' GROUP BY trips.IDTrip ORDER BY trips.name";

			@Override
			public PreparedStatement createPreparedStatement(Connection con) throws SQLException {

				PreparedStatement ps = con.prepareStatement( sql );
				ps.setInt( 1, UserManager.getInstance().getUser().getEvent_id() );
				return ps;
			}
		};
	}

	private PreparedStatementCreator getParticipantsStatesSt() {

		return new PreparedStatementCreator() {
			String sql = parseTimeMeasuredQuery( "SELECT states.name AS x, COUNT(users.state_id) AS y FROM jos_event_manager_users AS users JOIN jos_event_manager_states AS states ON users.state_id = states.IDState WHERE users.event_id = ? GROUP BY users.state_id ORDER BY states.name" );

			@Override
			public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
				PreparedStatement ps = con.prepareStatement( sql );
				ps.setInt( 1, UserManager.getInstance().getUser().getEvent_id() );
				return ps;
			}
		};
	}

	private PreparedStatementCreator getRegistrationSt() {
		return new PreparedStatementCreator() {
			String sql = parseTimeMeasuredQuery( "SELECT TIMEMEASURE(jos_users.registerDate) AS x, COUNT(jos_users.registerDate) AS y FROM jos_users JOIN jos_event_manager_users ON jos_event_manager_users.user_id = jos_users.id WHERE jos_event_manager_users.event_id = ? GROUP BY TIMEMEASURE(jos_users.registerDate) ORDER BY TIMEMEASURE(jos_users.registerDate)" );

			@Override
			public PreparedStatement createPreparedStatement(Connection con) throws SQLException {

				PreparedStatement ps = con.prepareStatement( sql );
				ps.setInt( 1, UserManager.getInstance().getUser().getEvent_id() );
				return ps;
			}
		};
	}

	private PreparedStatementCreator getLastVisitSt() {
		return new PreparedStatementCreator() {
			String sql = parseTimeMeasuredQuery( "SELECT TIMEMEASURE(u.lastvisitDate) AS x, COUNT(u.lastvisitDate) AS y FROM jos_users AS u JOIN jos_event_manager_users AS users ON users.user_id = u.id WHERE users.event_id = ? AND TIMEMEASURE(u.lastvisitDate)!=0 GROUP BY TIMEMEASURE(u.lastvisitDate) ORDER BY TIMEMEASURE(u.lastvisitDate)" );

			@Override
			public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
				PreparedStatement ps = con.prepareStatement( sql );
				ps.setInt( 1, UserManager.getInstance().getUser().getEvent_id() );
				return ps;
			}
		};
	}

	private PreparedStatementCreator getBirthDateSt() {
		return new PreparedStatementCreator() {

			String sql = parseTimeMeasuredQuery( "SELECT TIMEMEASURE(birthDate) AS x, COUNT(birthDate) AS y FROM jos_event_manager_users WHERE event_id = ? GROUP BY TIMEMEASURE(birthDate) ORDER BY TIMEMEASURE(birthDate)" );

			@Override
			public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
				PreparedStatement ps = con.prepareStatement( sql );
				ps.setInt( 1, UserManager.getInstance().getUser().getEvent_id() );
				return ps;
			}
		};
	}

	private PreparedStatementCreator getProjectScoreSt() {
		return new PreparedStatementCreator() {
			String sql = parseTimeMeasuredQuery( "SELECT e.project_id AS x, SUM(e.points) AS y FROM jos_event_manager_project_evaluation AS e WHERE e.event_id = ? GROUP BY e.project_id ORDER BY x" );

			@Override
			public PreparedStatement createPreparedStatement(Connection con) throws SQLException {

				PreparedStatement ps = con.prepareStatement( sql );
				ps.setInt( 1, UserManager.getInstance().getUser().getEvent_id() );
				return ps;
			}
		};
	}

	private PreparedStatementCreator getEsiTourVisitorsSt() {
		return new PreparedStatementCreator() {
			String sql = "SELECT t.name AS x, "
					+ "COUNT( u.esiTour_id ) AS y FROM jos_event_manager_trips AS t "
					+ " LEFT JOIN jos_event_manager_users AS u ON u.esiTour_id = t.IDTrip"
					+ " WHERE t.event_id = ? AND t.type = 'ESI_TOUR' GROUP BY t.IDTrip ORDER BY t.name";

			@Override
			public PreparedStatement createPreparedStatement(Connection con) throws SQLException {

				PreparedStatement ps = con.prepareStatement( sql );
				ps.setInt( 1, UserManager.getInstance().getUser().getEvent_id() );
				return ps;
			}
		};
	}

	/**
	 * Returns data in shape x and y variables. This is sufficient for creation of any type of graph.
	 * @return 
	 */
	public List<Coordinate> getGraphData() {
		List<Coordinate> coordinates = DatabaseManager.getInstance().getJdbcTemplate().query( getStatement(), new RowMapper<Coordinate>() {

			public Coordinate mapRow(ResultSet rs, int rowNum) throws SQLException {
				Coordinate tempCoordinate = new Coordinate( rs.getString( "x" ), rs.getInt( "y" ) );
				return tempCoordinate;
			}
		} );

		return coordinates;
	}

	public ImageIcon getIcon(ChartType chartType) {
		return new GoogleApi( getGraphData(), this.graphName, chartType ).getGraphIcon();
	}

	public void saveToXml(File file, String graphName) {
		new XmlGraphExporter( getGraphData(), graphName ).save( file );
	}

	private String parseTimeMeasuredQuery(String sql) {
		return sql.replaceAll( "TIMEMEASURE", this.graphGroupBy );
	}
}
